package com.dy.yunying.biz.dao.datacenter.impl;

import com.dy.yunying.api.datacenter.dto.AdDataDto;
import com.dy.yunying.api.datacenter.vo.AdDataVo;
import com.dy.yunying.biz.config.YunYingProperties;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.math.BigDecimal;
import java.util.*;

/**
 * @author ：lile
 * @date ：2021/6/17 14:05
 * @description：
 * @modified By：
 */
@Component(value = "dcAdDataDao")
@Slf4j
public class AdDataDao {

	@Resource(name = "clickDcSessionTemplate")
	private JdbcTemplate clickhouseTemplate;

	@Resource
	private YunYingProperties yunYingProperties;

	/**
	 * 求总数
	 *
	 * @param req
	 * @return
	 */
	public Long countDataTotal(AdDataDto req) {
		final String indentStr = "        ";
		StringBuilder countSql = new StringBuilder();
		StringBuilder sql = this.getSql(req, indentStr);
		countSql.append("SELECT COUNT(1) FROM (").append(sql).append(")");
		return clickhouseTemplate.queryForObject(countSql.toString(), Long.class);
	}

	public List<AdDataVo> selectAdDataSource(AdDataDto req) {
		final String indentStr = StringUtils.EMPTY;
		final StringBuilder sql = this.getSql(req, indentStr);

		if (StringUtils.isNotBlank(req.getKpiValue()) && StringUtils.isNotBlank(req.getSort())) {
			sql.append("ORDER BY\n");
			sql.append("    ").append(req.getKpiValue()).append(" ").append(req.getSort()).append('\n');
		} else {
			sql.append("ORDER BY\n");
			sql.append("    period DESC\n");
		}
		Long current = req.getCurrent();
		Long size = req.getSize();
		if (Objects.nonNull(current) && Objects.nonNull(size)) {
			Long offset = (current - 1) * size;
			sql.append("LIMIT\n");
			sql.append("    ").append(offset).append(", ").append(size).append('\n');
		}

		log.info("广告数据报表分页查询SQL: [\n{}]", sql.toString());

		List<AdDataVo> data = new ArrayList<>();

		long start = System.currentTimeMillis();
		SqlRowSet rs = clickhouseTemplate.queryForRowSet(sql.toString());
		long end = System.currentTimeMillis();
		log.info("广告数据报表分页查询耗时: {}ms", end - start);

		List<String> columns = Arrays.asList(rs.getMetaData().getColumnNames());

		//遍历 ResultSet
		while (rs.next()) {
			//组装成对象 添加到结果集
			AdDataVo item = ObjByRow(rs, columns);
			data.add(item);
		}

		return data;
	}

	private StringBuilder getSql(AdDataDto req, String indentStr) {
		StringBuilder sql = new StringBuilder();
		sql.append(indentStr).append("SELECT\n");
		sql.append(indentStr).append("    ").append(this.getPeriodSql(req)).append(" AS period, ").append(this.getQueryColumnSql(req)).append(req.getShowRatio()).append(" AS showRatio,");
		sql.append(indentStr).append("    round(rudeCost, 2) rudeCost, -- 原始消耗\n");
		sql.append(indentStr).append("    round(cost, 2) cost, -- 返点后消耗\n");
		sql.append(indentStr).append("    COALESCE(uuidnums, 0) deviceCount, -- 新增设备数\n");
		sql.append(indentStr).append("    COALESCE(usrnamenums, 0) usrnamenums, -- 新增设备注册数\n");
		sql.append(indentStr).append("    newUserCount, -- 新增用户数\n");
		sql.append(indentStr).append("    createRoleCount, -- 新增注册创角数\n");
		sql.append(indentStr).append("    certifiedCount, -- 新增注册实名数\n");
		sql.append(indentStr).append("    notCertifiedCount, -- 注册未实名数\n");
		sql.append(indentStr).append("    youngCount, -- 未成年人数\n");
		sql.append(indentStr).append("    COALESCE(retention2, 0) num1, -- 次留人数\n");
		sql.append(indentStr).append("    COALESCE(usrpaynamenums, 0) newPayCount, -- 新增付费设备数\n");
		sql.append(indentStr).append("    if(showRatio = 1, toDecimal64OrZero(toString(newdevicesharfee), 2), toDecimal64OrZero(toString(newdevicefees), 2)) newPayAmount, -- 新增充值实付金额\n");
		sql.append(indentStr).append("    if(showRatio = 1, toDecimal64OrZero(toString(newdeviceshargivemoney),2), toDecimal64OrZero(toString(newdevicegivemoney),2)) newPayGivemoney, -- 新增充值代金券金额\n");
		sql.append(indentStr).append("    COALESCE(weektotalfeenums, 0) weekPayCount, -- 当周付费设备数\n");
		sql.append(indentStr).append("    if(showRatio = 1, toDecimal64OrZero(toString(weeksharfee), 2), toDecimal64OrZero(toString(weektotalfee), 2)) weektotalfee, -- 当周充值实付金额\n");
		sql.append(indentStr).append("    if(showRatio = 1, toDecimal64OrZero(toString(weekshargivemoney), 2), toDecimal64OrZero(toString(weektotalgivemoney), 2)) weekPayGivemoney, -- 当周充值代金券金额\n");
		sql.append(indentStr).append("    COALESCE(monthtotalfeenums, 0) monthPayCount, -- 当月付费设备数\n");
		sql.append(indentStr).append("    if(showRatio = 1, toDecimal64OrZero(toString(monthsharfee), 2), toDecimal64OrZero(toString(monthtotalfee), 2)) monthtotalfee, -- 当月充值实付金额\n");
		sql.append(indentStr).append("    if(showRatio = 1, toDecimal64OrZero(toString(monthshargivemoney), 2), toDecimal64OrZero(toString(monthtotalgivemoney), 2)) monthPayGivemoney, -- 当月充值代金券金额\n");
		sql.append(indentStr).append("    periodPayCount, -- 期内付费设备数\n");
		sql.append(indentStr).append("    round(IF(toInt64(periodPayCount) > 0, divide(toFloat64(cost), periodPayCount), 0), 2) AS periodPayCose, -- 期内付费成本\n");
		sql.append(indentStr).append("    duplicateDeviceCount, -- 重复设备数\n");
		sql.append(indentStr).append("    round(IF(toInt64(usrnamenums) > 0, divide(duplicateDeviceCount * 100.00, usrnamenums), 0), 2) AS duplicateDeviceRatio, -- 设备重复率\n");
		sql.append(indentStr).append("    IF(showRatio = 1, toDecimal64OrZero(toString(periodPayFee2), 4), toDecimal64OrZero(toString(periodPayFee1), 4)) AS periodPayFee, -- 期内充值实付金额\n");
		sql.append(indentStr).append("    IF(showRatio = 1, toDecimal64OrZero(toString(periodPayGivemoney2), 4), toDecimal64OrZero(toString(periodPayGivemoney1), 4)) AS periodPayGivemoney, -- 期内充值代金券金额\n");
		sql.append(indentStr).append("    COALESCE(totalPayfeenums, 0) totalPay, -- 累计付费设备数\n");
		sql.append(indentStr).append("    if(showRatio = 1, toDecimal64OrZero(toString(totalPaysharfee), 2), toDecimal64OrZero(toString(totalPayfee), 2)) totalPayfee, -- 累计充值实付金额\n");
		sql.append(indentStr).append("    if(showRatio = 1, toDecimal64OrZero(toString(totalPayshargivemoney), 2), toDecimal64OrZero(toString(totalPaygivemoney), 2)) totalPayGivemoney, -- 累计充值代金券金额\n");
		sql.append(indentStr).append("    COALESCE(activedevices, 0) activedevices, -- 活跃设备数\n");
		sql.append(indentStr).append("    COALESCE(activepaydevices, 0) activePayCount, -- 活跃付费设备数\n");
		sql.append(indentStr).append("    if(showRatio = 1, toDecimal64OrZero(toString(activesharfee), 2), toDecimal64OrZero(toString(activetotalfee), 2)) activetotalfee, -- 活跃充值实付金额\n");
		sql.append(indentStr).append("    if(showRatio = 1, toDecimal64OrZero(toString(activeshargivemoney), 2), toDecimal64OrZero(toString(activetotalgivemoney), 2)) activePayGivemoney, -- 活跃充值代金券金额\n");
		sql.append(indentStr).append("    round(if(toInt64(usrnamenums) > 0, divide(toFloat64(cost), usrnamenums), 0), 2) deviceCose, -- 设备成本\n");
		sql.append(indentStr).append("    round(IF(toInt64(usrnamenums) > 0, divide(createRoleCount * 100.00, usrnamenums), 0), 2) AS createRoleRate, -- 新增注册创角率\n");
		sql.append(indentStr).append("    round(IF(toInt64(newUserCount) > 0, divide(certifiedCount * 100.00, newUserCount), 0), 2) AS certifiedRate, -- 新增实名制转化率\n");
		sql.append(indentStr).append("    round(if(toInt64(usrnamenums) > 0, divide(retention2 * 100.00, usrnamenums), 0), 2) retention2Ratio, -- 次留\n");
		sql.append(indentStr).append("    round(if(toInt64(usrnamenums) > 0, divide(usrpaynamenums * 100, usrnamenums), 0), 2) regPayRatio, -- 新增付费率\n");
		sql.append(indentStr).append("    round(IF(toInt64(usrnamenums) > 0, divide(periodPayCount * 100.00, usrnamenums), 0), 2) AS periodPayRate, -- 期内付费率\n");
		sql.append(indentStr).append("    round(IF(toInt64(activedevices) > 0, divide(activepaydevices * 100.00, activedevices), 0), 2) AS activePayRate, -- 活跃付费率\n");
		sql.append(indentStr).append("    IF(showRatio = 1, round(if(toInt64(usrnamenums) > 0, divide(toFloat64(newdevicesharfee), usrnamenums), 0), 2), round(if(toInt64(usrnamenums) > 0, divide(toFloat64(newdevicefees), usrnamenums), 0), 2)) regarpu, -- 新增注册ARPU\n");
		sql.append(indentStr).append("    IF(showRatio = 1, round(IF(toInt64(usrpaynamenums) > 0, divide(toFloat64(newdevicesharfee), usrpaynamenums), 0), 2),round(IF(toInt64(usrpaynamenums) > 0, divide(toFloat64(newdevicefees), usrpaynamenums), 0), 2)) AS newPayArppu, -- 新增付费ARPPU\n");
		sql.append(indentStr).append("    IF(showRatio = 1, round(if(toInt64(activedevices) > 0, divide(toFloat64(activesharfee), activedevices), 0), 2), round(if(toInt64(activedevices) > 0, divide(toFloat64(activetotalfee), activedevices), 0), 2)) actarpu, -- 活跃设备ARPU\n");
		sql.append(indentStr).append("    IF(showRatio = 1, round(IF(toInt64(activePayCount) > 0, divide(toFloat64(activesharfee), activePayCount), 0), 2), round(IF(toInt64(activePayCount) > 0, divide(toFloat64(activetotalfee), activePayCount), 0), 2)) activePayArppu, -- 活跃付费ARPPU\n");
		sql.append(indentStr).append("    IF(showRatio = 1, round(if(cost > 0, divide(newdevicesharfee * 100.00, cost), 0), 2), round(if(cost > 0, divide(newdevicefees * 100.00, cost), 0), 2)) roi1, -- 首日ROI\n");
		sql.append(indentStr).append("    IF(showRatio = 1, round(if(cost > 0, divide(weeksharfee * 100.00, cost), 0), 2), round(if(cost > 0, divide(weektotalfee * 100.00, cost), 0), 2)) weekRoi, -- 当周ROI\n");
		sql.append(indentStr).append("    IF(showRatio = 1, round(if(cost > 0, divide(monthsharfee * 100.00, cost), 0), 2), round(if(cost > 0, divide(monthtotalfee * 100.00, cost), 0), 2)) monthRoi, -- 当月ROI\n");
		sql.append(indentStr).append("    IF(showRatio = 1, round(if(cost > 0, divide(totalPaysharfee * 100.00, cost), 0), 2), round(if(cost > 0, divide(totalPayfee * 100.00, cost), 0), 2)) allRoi -- 累计ROI\n");
		sql.append(indentStr).append("FROM\n");
		sql.append(indentStr).append("    (\n");
		sql.append(indentStr).append("        SELECT\n");
		sql.append(indentStr).append("            ").append(this.getPeriodSql(req)).append(", ").append(this.getQueryColumnSql(req)).append('\n');
		sql.append(indentStr).append("            toFloat64(rudeCost) rudeCost, -- 原始消耗\n");
		sql.append(indentStr).append("            toFloat64(cost) cost, -- 返点后消耗\n");
		sql.append(indentStr).append("            uuidnums, -- 新增设备数\n");
		sql.append(indentStr).append("            usrnamenums, -- 新增设备注册数\n");
		sql.append(indentStr).append("            retention2, -- 留存\n");
		sql.append(indentStr).append("            usrpaynamenums, -- 新增设备付费数\n");
		sql.append(indentStr).append("            toFloat64(newdevicefees) newdevicefees, -- 新增充值实付金额（分成前）\n");
		sql.append(indentStr).append("            toFloat64(newdevicesharfee) newdevicesharfee, -- 新增充值实付金额（分成后）\n");
		sql.append(indentStr).append("            toFloat64(newdevicegivemoney) newdevicegivemoney, -- 新增充值代金券金额（分成前）\n");
		sql.append(indentStr).append("            toFloat64(newdeviceshargivemoney) newdeviceshargivemoney, -- 新增充值代金券金额（分成后）\n");
		sql.append(indentStr).append("            weektotalfeenums, -- 当周充值人数\n");
		sql.append(indentStr).append("            toFloat64(weektotalfee) weektotalfee, -- 当周充值实付金额（分成前）\n");
		sql.append(indentStr).append("            toFloat64(weeksharfee) weeksharfee, -- 当周充值实付金额（分成后）\n");
		sql.append(indentStr).append("            toFloat64(weektotalgivemoney) weektotalgivemoney, -- 当周充值代金券金额（分成前）\n");
		sql.append(indentStr).append("            toFloat64(weekshargivemoney) weekshargivemoney, -- 当周充值代金券金额（分成后）\n");
		sql.append(indentStr).append("            monthtotalfeenums, -- 当月充值人数\n");
		sql.append(indentStr).append("            toFloat64(monthtotalfee) monthtotalfee, -- 当月充值实付金额（分成前）\n");
		sql.append(indentStr).append("            toFloat64(monthsharfee) monthsharfee, -- 当月充值实付金额（分成后）\n");
		sql.append(indentStr).append("            toFloat64(monthtotalgivemoney) monthtotalgivemoney, -- 当月充值代金券金额（分成前）\n");
		sql.append(indentStr).append("            toFloat64(monthshargivemoney) monthshargivemoney, -- 当月充值代金券金额（分成后）\n");
		sql.append(indentStr).append("            duplicateDeviceCount, -- 重复设备数\n");
		sql.append(indentStr).append("            periodPayCount, -- 期内设备付费数\n");
		sql.append(indentStr).append("            periodPayFee1, -- 期内充值实付金额（分成前）\n");
		sql.append(indentStr).append("            periodPayFee2, -- 期内充值实付金额（分成后）\n");
		sql.append(indentStr).append("            periodPayGivemoney1, -- 期内充值代金券金额（分成前）\n");
		sql.append(indentStr).append("            periodPayGivemoney2, -- 期内充值代金券金额（分成后）\n");
		sql.append(indentStr).append("            duplicateDeviceCount, -- 重复设备数\n");
		sql.append(indentStr).append("            totalPayfeenums, -- 累计充值人数\n");
		sql.append(indentStr).append("            toFloat64(totalPayfee) totalPayfee, -- 累计充值实付金额（分成前）\n");
		sql.append(indentStr).append("            toFloat64(totalPaysharfee) totalPaysharfee, -- 累计充值实付金额（分成后）\n");
		sql.append(indentStr).append("            toFloat64(totalPaygivemoney) totalPaygivemoney, -- 累计充值代金券金额（分成前）\n");
		sql.append(indentStr).append("            toFloat64(totalPayshargivemoney) totalPayshargivemoney, -- 累计充值代金券金额（分成后）\n");
		sql.append(indentStr).append("            newUserCount, -- 新增用户数\n");
		sql.append(indentStr).append("            createRoleCount, -- 新增注册创角数\n");
		sql.append(indentStr).append("            certifiedCount, -- 新增注册实名数\n");
		sql.append(indentStr).append("            notCertifiedCount, -- 注册未实名数\n");
		sql.append(indentStr).append("            youngCount -- 未成年人数\n");
		sql.append(indentStr).append("        FROM\n");
		sql.append(indentStr).append("            (\n");
		sql.append(indentStr).append("                SELECT\n");
		sql.append(indentStr).append("                    ").append(this.getPeriodSql(req)).append(", ").append(this.getQueryColumnSql(req)).append('\n');
		sql.append(indentStr).append("                    rudeCost, -- 原始消耗\n");
		sql.append(indentStr).append("                    cost, -- 返点后消耗\n");
		sql.append(indentStr).append("                    uuidnums, -- 新增设备数\n");
		sql.append(indentStr).append("                    usrnamenums, -- 新增设备注册数\n");
		sql.append(indentStr).append("                    retention2, -- 留存\n");
		sql.append(indentStr).append("                    usrpaynamenums, -- 新增设备付费数\n");
		sql.append(indentStr).append("                    newdevicefees, -- 新增充值实付金额（分成前）\n");
		sql.append(indentStr).append("                    newdevicesharfee, -- 新增充值实付金额（分成后）\n");
		sql.append(indentStr).append("                    newdevicegivemoney, -- 新增充值代金券金额（分成前）\n");
		sql.append(indentStr).append("                    newdeviceshargivemoney, -- 新增充值代金券金额（分成后）\n");
		sql.append(indentStr).append("                    weektotalfeenums, -- 当周充值人数\n");
		sql.append(indentStr).append("                    weektotalfee, -- 当周充值实付金额（分成前）\n");
		sql.append(indentStr).append("                    weeksharfee, -- 当周充值实付金额（分成后）\n");
		sql.append(indentStr).append("                    weektotalgivemoney, -- 当周充值代金券金额（分成前）\n");
		sql.append(indentStr).append("                    weekshargivemoney, -- 当周充值代金券金额（分成后）\n");
		sql.append(indentStr).append("                    monthtotalfeenums, -- 当月充值人数\n");
		sql.append(indentStr).append("                    monthtotalfee, -- 当月充值实付金额（分成前）\n");
		sql.append(indentStr).append("                    monthsharfee, -- 当月充值实付金额（分成后）\n");
		sql.append(indentStr).append("                    monthtotalgivemoney, -- 当月充值代金券金额（分成前）\n");
		sql.append(indentStr).append("                    monthshargivemoney, -- 当月充值代金券金额（分成后）\n");
		sql.append(indentStr).append("                    duplicateDeviceCount, -- 重复设备数\n");
		sql.append(indentStr).append("                    totalPayfeenums, -- 累计充值人数\n");
		sql.append(indentStr).append("                    totalPayfee, -- 累计充值实付金额（分成前）\n");
		sql.append(indentStr).append("                    totalPaysharfee, -- 累计充值实付金额（分成后）\n");
		sql.append(indentStr).append("                    totalPaygivemoney, -- 累计充值代金券金额（分成前）\n");
		sql.append(indentStr).append("                    totalPayshargivemoney, -- 累计充值代金券金额（分成后）\n");
		sql.append(indentStr).append("                    newUserCount, -- 新增用户数\n");
		sql.append(indentStr).append("                    createRoleCount, -- 新增注册创角数\n");
		sql.append(indentStr).append("                    certifiedCount, -- 新增注册实名数\n");
		sql.append(indentStr).append("                    notCertifiedCount, -- 注册未实名数\n");
		sql.append(indentStr).append("                    youngCount -- 未成年人数\n");
		sql.append(indentStr).append("                FROM\n");
		sql.append(indentStr).append("                    (\n");
		sql.append(indentStr).append("                        SELECT\n");
		sql.append(indentStr).append("                            ").append(this.getPeriodSql(req)).append(", ").append(this.getQueryColumnSql(req)).append('\n');
		sql.append(indentStr).append("                            usrnamenums, -- 新增设备注册数\n");
		sql.append(indentStr).append("                            uuidnums, -- 新增设备\n");
		sql.append(indentStr).append("                            retention2, -- 次留\n");
		sql.append(indentStr).append("                            usrpaynamenums, -- 新增设备付费数\n");
		sql.append(indentStr).append("                            newdevicefees, -- 新增充值实付金额（分成前）\n");
		sql.append(indentStr).append("                            newdevicesharfee, -- 新增充值实付金额（分成后）\n");
		sql.append(indentStr).append("                            newdevicegivemoney, -- 新增充值代金券金额（分成前）\n");
		sql.append(indentStr).append("                            newdeviceshargivemoney, -- 新增充值代金券金额（分成后）\n");
		sql.append(indentStr).append("                            weektotalfeenums, -- 当周充值人数\n");
		sql.append(indentStr).append("                            weektotalfee, -- 当周充值实付金额（分成前）\n");
		sql.append(indentStr).append("                            weeksharfee, -- 当周充值实付金额（分成后）\n");
		sql.append(indentStr).append("                            weektotalgivemoney, -- 当周充值代金券金额（分成前）\n");
		sql.append(indentStr).append("                            weekshargivemoney, -- 当周充值代金券金额（分成后）\n");
		sql.append(indentStr).append("                            monthtotalfeenums, -- 当月充值人数\n");
		sql.append(indentStr).append("                            monthtotalfee, -- 当月充值实付金额（分成前）\n");
		sql.append(indentStr).append("                            monthsharfee, -- 当月充值实付金额（分成后）\n");
		sql.append(indentStr).append("                            monthtotalgivemoney, -- 当月充值代金券金额（分成前）\n");
		sql.append(indentStr).append("                            monthshargivemoney, -- 当月充值代金券金额（分成后）\n");
		sql.append(indentStr).append("                            duplicateDeviceCount, -- 重复设备数\n");
		sql.append(indentStr).append("                            totalPayfeenums, -- 累计充值人数\n");
		sql.append(indentStr).append("                            totalPayfee, -- 累计充值实付金额（分成前）\n");
		sql.append(indentStr).append("                            totalPaysharfee, -- 累计充值实付金额（分成后）\n");
		sql.append(indentStr).append("                            totalPaygivemoney, -- 累计充值代金券金额（分成前）\n");
		sql.append(indentStr).append("                            totalPayshargivemoney, -- 累计充值代金券金额（分成后）\n");
		sql.append(indentStr).append("                            newUserCount, -- 新增用户数\n");
		sql.append(indentStr).append("                            createRoleCount, -- 新增注册创角数\n");
		sql.append(indentStr).append("                            certifiedCount, -- 新增注册实名数\n");
		sql.append(indentStr).append("                            notCertifiedCount, -- 注册未实名数\n");
		sql.append(indentStr).append("                            youngCount -- 未成年人数\n");
		sql.append(indentStr).append("                        FROM\n");
		sql.append(indentStr).append("                            (\n");
		sql.append(indentStr).append(this.getDevicePaySql(req, indentStr + "                                "));
		sql.append(indentStr).append("                            ) a\n");
		sql.append(indentStr).append("                            FULL JOIN (\n");
		sql.append(indentStr).append(this.getCertifiedSql(req, indentStr + "                                "));
		sql.append(indentStr).append("                            ) b USING (").append(this.getPeriodSql(req)).append(this.getGroupColumnSql(req)).append(")\n"); // USING 条件
		sql.append(indentStr).append("                    ) c\n");
		sql.append(indentStr).append("                    FULL JOIN (\n");
		sql.append(indentStr).append(this.getCostSql(req, indentStr + "                        "));
		sql.append(indentStr).append("                    ) d USING (").append(this.getPeriodSql(req)).append(this.getGroupColumnSql(req)).append(")\n"); // USING 条件
		sql.append(indentStr).append("            ) e\n");
		sql.append(indentStr).append("            FULL JOIN (\n");
		sql.append(indentStr).append(this.getPeriodPaySql(req, indentStr + "                "));
		sql.append(indentStr).append("            ) f USING (").append(this.getPeriodSql(req)).append(this.getGroupColumnSql(req)).append(")\n"); // USING 条件
		sql.append(indentStr).append("    ) g\n");
		sql.append(indentStr).append("    FULL JOIN (\n");
		sql.append(indentStr).append(this.getActiveDeviceSql(req, indentStr + "        "));
		sql.append(indentStr).append("    ) h USING (").append(this.getPeriodSql(req)).append(this.getGroupColumnSql(req)).append(")\n"); // USING 条件
		sql.append(indentStr).append("WHERE\n");
		sql.append(indentStr).append("    1 = 1\n");
		return sql;
	}


	//新增充值金额和新增设备付费数,当周充值金额，当月充值金额，累计充值金额
	private StringBuilder getDevicePaySql(AdDataDto req, String indentStr) {
		StringBuilder deviceRegSql = new StringBuilder();
		deviceRegSql.append(indentStr).append("SELECT\n");
		deviceRegSql.append(indentStr).append("    ").append(this.getPeriodSql(req)).append(", ").append(this.getQueryColumnSql(req)).append('\n');
		deviceRegSql.append(indentStr).append("    COALESCE(COUNT(IF(reg.latest_username != '', reg.latest_username, NULL)), 0) usrnamenums, -- 新增设备注册数\n");
		deviceRegSql.append(indentStr).append("    COUNT(DISTINCT reg.kid) uuidnums, -- 新增设备\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(is_2_retention), 0) retention2, -- 次留\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(IF(reg.fee_1 > 0 OR reg.givemoney_1 > 0, 1, 0)), 0) usrpaynamenums, -- 新增设备付费数\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(reg.fee_1), 0) newdevicefees, -- 新增充值实付金额（分成前）\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(reg.fee_1 * sharing), 0) newdevicesharfee, -- 新增充值实付金额（分成后）\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(reg.givemoney_1), 0) newdevicegivemoney, -- 新增充值代金券金额（分成前）\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(reg.givemoney_1 * sharing), 0) newdeviceshargivemoney, -- 新增充值代金券金额（分成后）\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(IF(reg.fee_week > 0 OR reg.givemoney_week > 0, 1, 0)), 0) weektotalfeenums, -- 当周充值人数\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(reg.fee_week), 0) weektotalfee, -- 当周充值实付金额（分成前）\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(reg.fee_week * sharing), 0) weeksharfee, -- 当周充值实付金额（分成后）\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(reg.givemoney_week), 0) weektotalgivemoney, -- 当周充值代金券金额（分成前）\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(reg.givemoney_week * sharing), 0) weekshargivemoney, -- 当周充值代金券金额（分成后）\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(IF(reg.fee_month > 0 OR reg.givemoney_month > 0, 1, 0)), 0) monthtotalfeenums, -- 当月充值人数\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(reg.fee_month), 0) monthtotalfee, -- 当月充值实付金额（分成前）\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(reg.fee_month * sharing), 0) monthsharfee, -- 当月充值实付金额（分成后）\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(reg.givemoney_month), 0) monthtotalgivemoney, -- 当月充值代金券金额（分成前）\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(reg.givemoney_month * sharing), 0) monthshargivemoney, -- 当月充值代金券金额（分成后）\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(IF(reg.fee_total > 0 OR reg.givemoney_total > 0, 1, 0)), 0)  totalPayfeenums, -- 累计充值人数\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(reg.fee_total), 0) totalPayfee, -- 累计充值实付金额（分成前）\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(reg.duplicate_device), 0) duplicateDeviceCount, -- 重复设备数\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(reg.fee_total * sharing), 0) totalPaysharfee, -- 累计充值实付金额（分成后）\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(reg.givemoney_total), 0) totalPaygivemoney, -- 累计充值代金券金额（分成前）\n");
		deviceRegSql.append(indentStr).append("    COALESCE(SUM(reg.givemoney_total * sharing), 0) totalPayshargivemoney -- 累计充值代金券金额（分成后）\n");
		deviceRegSql.append(indentStr).append("FROM\n");
		deviceRegSql.append(indentStr).append("    (\n");
		deviceRegSql.append(indentStr).append("        SELECT\n");
		deviceRegSql.append(indentStr).append("            reg_day day, week,month, year, kid, collect, game_main pgid, os, game_sub gameid, chl_main parentchl, chl_sub chl, chl_base appchl, ad_id adid, ad_account advertiserid, latest_username,\n");
		deviceRegSql.append(indentStr).append("            is_2_retention, fee_1, givemoney_1, fee_week, givemoney_week, fee_month, givemoney_month,IF(before_last_login_time_diff > 0, 1, 0) AS duplicate_device, fee_total, givemoney_total\n");
		deviceRegSql.append(indentStr).append("        FROM\n");
		deviceRegSql.append(indentStr).append("            ").append(yunYingProperties.getNinetydeviceregtable()).append("\n");
		deviceRegSql.append(indentStr).append("        WHERE\n");
		deviceRegSql.append(indentStr).append("            spread_type = 1 AND reg_day >= ").append(req.getSTime()).append(" AND reg_day <= ").append(req.getETime()).append(this.directDeviceRegisterWhereSnippet(req, " ")).append('\n');
		deviceRegSql.append(indentStr).append("    ) reg\n");
		deviceRegSql.append(indentStr).append("    LEFT JOIN (SELECT CAST(id AS Int16) AS id, (1 - COALESCE(sharing, 0)) sharing FROM dim_200_pangu_mysql_parent_game pg_tmp) pg ON reg.pgid = pg.id\n");
		deviceRegSql.append(indentStr).append("    LEFT JOIN (SELECT manage investor, parent_code, chncode, dept_id deptId, dept_group_id userGroupId, real_name investorName, dept_name deptName, COALESCE(name,'-') userGroupName FROM v_dim_200_pangu_channel_user_dept_group) wpc ON wpc.parent_code = reg.parentchl AND wpc.chncode = reg.chl\n");
		deviceRegSql.append(indentStr).append("WHERE\n");
		deviceRegSql.append(indentStr).append("    1 = 1");
		if (req.getIsSys() != 1) {
			deviceRegSql.append(" AND investor IN (").append(req.getUserIds()).append(")"); // -- 渠道权限
		}
		deviceRegSql.append(this.selectComConditionWd(req, "")).append('\n');
		deviceRegSql.append(indentStr).append("GROUP BY\n"); // group by 条件
		deviceRegSql.append(indentStr).append("    ").append(this.getPeriodSql(req)).append(this.getGroupColumnSql(req));
		if (StringUtils.isNotBlank(req.getQueryColumn())) {
			deviceRegSql.append(", ").append(req.getQueryColumn());
		}
		deviceRegSql.append('\n');
		return deviceRegSql;
	}


	private StringBuilder getCertifiedSql(AdDataDto req, String indentStr) {
		StringBuilder certifiedSql = new StringBuilder();
		certifiedSql.append(indentStr).append("SELECT\n");
		certifiedSql.append(indentStr).append("    ").append(this.getPeriodSql(req)).append(", ").append(this.getQueryColumnSql(req)).append('\n');
		certifiedSql.append(indentStr).append("    COALESCE(COUNT(username), 0) AS newUserCount, -- 新增用户数\n");
		certifiedSql.append(indentStr).append("    COALESCE(SUM(createRoleCount), 0) AS createRoleCount, -- 新增注册创角数\n");
		certifiedSql.append(indentStr).append("    COALESCE(SUM(certifiedMark), 0) AS certifiedCount, -- 新增注册实名数\n");
		certifiedSql.append(indentStr).append("    (newUserCount - certifiedCount) AS notCertifiedCount, -- 注册未实名数\n");
		certifiedSql.append(indentStr).append("    COALESCE(SUM(youngMark), 0) AS youngCount -- 未成年人数\n");
		certifiedSql.append(indentStr).append("FROM\n");
		certifiedSql.append(indentStr).append("    (\n");
		certifiedSql.append(indentStr).append("        SELECT\n");
		certifiedSql.append(indentStr).append("            MIN(d_reg_day) day, any(d_reg_week) week, any(d_reg_month) month, any(d_reg_year) year, any(d_kid) kid, any(collect) collect, d_game_main pgid, argMin(d_os, reg_time) os, argMin(d_game_sub, reg_time) gameid,\n");
		certifiedSql.append(indentStr).append("            argMin(d_chl_main, reg_time) parentchl, argMin(d_chl_sub, reg_time) chl, argMin(d_chl_base, reg_time) appchl, argMin(d_ad_id, reg_time) adid, argMin(d_ad_account, reg_time) advertiserid,\n");
		certifiedSql.append(indentStr).append("            argMin(d_convert_name, reg_time) convertName, argMin(d_deep_convert, reg_time) deepConvert, argMin(d_convert_data_type, reg_time) convertDataType, username\n");
		certifiedSql.append(indentStr).append("        FROM\n");
		certifiedSql.append(indentStr).append("            ").append(yunYingProperties.getAccountregistertable()).append(" acc\n");
		certifiedSql.append(indentStr).append("        WHERE\n");
		certifiedSql.append(indentStr).append("            d_spread_type = 1 AND d_reg_day >= ").append(req.getSTime()).append(" AND d_reg_day <= ").append(req.getETime()).append(this.indirectDeviceRegisterWhereSnippet(req, " ")).append('\n');
		certifiedSql.append(indentStr).append("        GROUP BY\n");
		certifiedSql.append(indentStr).append("            pgid, username\n");
		certifiedSql.append(indentStr).append("    ) acc\n");
		certifiedSql.append(indentStr).append("    LEFT JOIN (\n");
		certifiedSql.append(indentStr).append("        SELECT\n");
		certifiedSql.append(indentStr).append("            toInt16(pgame_id) AS pgame_id, user_name, COUNT(role_id) AS createRoleCount\n");
		certifiedSql.append(indentStr).append("        FROM\n");
		certifiedSql.append(indentStr).append("            (SELECT pgame_id, role_id, argMin(username, create_time) AS user_name FROM v_odsmysql_game_role GROUP BY pgame_id, role_id)\n");
		certifiedSql.append(indentStr).append("        GROUP BY\n");
		certifiedSql.append(indentStr).append("            pgame_id, user_name\n");
		certifiedSql.append(indentStr).append("    ) cr ON acc.pgid = cr.pgame_id AND acc.username = cr.user_name\n");
		certifiedSql.append(indentStr).append("    LEFT JOIN (\n");
		certifiedSql.append(indentStr).append("        SELECT\n");
		certifiedSql.append(indentStr).append("            toInt16(pgid) AS pgame_id, ai user_name, argMax(id_num, check_time) AS id_num, 1 AS certifiedMark, IF(toDate(addYears(parseDateTimeBestEffortOrZero(SUBSTRING(id_num, 7, 8)), 18)) >= today(), 1, 0) youngMark\n");
		certifiedSql.append(indentStr).append("        FROM\n");
		certifiedSql.append(indentStr).append("            v_odsmysql_wan_user_realname\n");
		certifiedSql.append(indentStr).append("        WHERE\n");
		certifiedSql.append(indentStr).append("            status <> 2\n");
		certifiedSql.append(indentStr).append("        GROUP BY\n");
		certifiedSql.append(indentStr).append("            pgame_id, user_name\n");
		certifiedSql.append(indentStr).append("    ) rel ON acc.pgid = rel.pgame_id AND acc.username = rel.user_name\n");
		certifiedSql.append(indentStr).append("    LEFT JOIN (SELECT manage investor, parent_code, chncode, dept_id deptId, dept_group_id userGroupId, real_name investorName, dept_name deptName, COALESCE(name,'-') userGroupName FROM v_dim_200_pangu_channel_user_dept_group) wpc ON wpc.parent_code = acc.parentchl AND wpc.chncode = acc.chl\n");
		certifiedSql.append(indentStr).append("WHERE\n");
		certifiedSql.append(indentStr).append("    1 = 1");
		if (req.getIsSys() != 1) {
			certifiedSql.append(" AND investor IN (").append(req.getUserIds()).append(")"); // -- 渠道权限
		}
		certifiedSql.append(this.selectComConditionWd(req, "")).append('\n');
		certifiedSql.append(indentStr).append("GROUP BY\n");
		// group by 条件
		certifiedSql.append(indentStr).append("    ").append(this.getPeriodSql(req)).append(this.getGroupColumnSql(req));
		if (StringUtils.isNotBlank(req.getQueryColumn())) {
			certifiedSql.append(", ").append(req.getQueryColumn());
		}
		certifiedSql.append('\n');
		return certifiedSql;
	}


	//	消耗、返点后消耗
	private StringBuilder getCostSql(AdDataDto req, String indentStr) {
		StringBuilder costSql = new StringBuilder();
		costSql.append(indentStr).append("SELECT\n");
		costSql.append(indentStr).append("    ").append(this.getPeriodSql(req)).append(", ").append(this.getQueryColumnSql(req)).append('\n');
		costSql.append(indentStr).append("    COALESCE(SUM(rudeCost), 0) rudeCost, -- 原始消耗\n");
		costSql.append(indentStr).append("    COALESCE(SUM(cost), 0) cost -- 返点后消耗\n");
		costSql.append(indentStr).append("FROM\n");
		costSql.append(indentStr).append("    (\n");
		costSql.append(indentStr).append("        SELECT\n");
		costSql.append(indentStr).append("            day, week, month, year, collect, ad_show shownums, click clicknums, ad_id adid, ad_account advertiserid, COALESCE(rude_cost, 0) rudeCost, COALESCE(cost, 0) cost\n");
		costSql.append(indentStr).append("        FROM\n");
		costSql.append(indentStr).append("            ").append(yunYingProperties.getAdidrebatetable()).append("\n");
		costSql.append(indentStr).append("        WHERE\n");
		costSql.append(indentStr).append("            day >= ").append(req.getSTime()).append(" AND day <= ").append(req.getETime());
		if (req.getIsSys() != 1) {
			// 广告账户权限权限AdAccounts返回空
			if (!"'NO'".equals(req.getAdAccounts())) {
				costSql.append(" AND ad_account IN (").append(req.getAdAccounts()).append(")");// -- 广告权限
			}
		}
		costSql.append('\n');
		costSql.append(indentStr).append("    ) ard\n");
		costSql.append(indentStr).append("    LEFT JOIN (SELECT os, game_main pgid, game_sub gameid, chl_main parentchl, chl_sub chl, chl_base appchl, ad_id FROM ").append(yunYingProperties.getAdptypetable()).append(") ap ON ard.adid = ap.ad_id\n");
		costSql.append(indentStr).append("    LEFT JOIN (SELECT manage investor, parent_code, chncode, dept_id deptId, dept_group_id userGroupId, real_name investorName, dept_name deptName, COALESCE(name, '-') userGroupName FROM v_dim_200_pangu_channel_user_dept_group) wpc ON wpc.parent_code = ap.parentchl AND wpc.chncode = ap.chl\n");
		costSql.append(indentStr).append("WHERE\n");
		costSql.append(indentStr).append("    1 = 1").append(this.selectComConditionWd(req, "")).append(this.selectCommonCondition(req, "")).append('\n');
		costSql.append(indentStr).append("GROUP BY\n");
		costSql.append(indentStr).append("    ").append(this.getPeriodSql(req)).append(this.getGroupColumnSql(req)).append('\n');// group by 条件
		return costSql;
	}


	private StringBuilder getPeriodPaySql(AdDataDto req, String indentStr) {
		StringBuilder activeDeviceSql = new StringBuilder();
		activeDeviceSql.append(indentStr).append("SELECT\n");
		activeDeviceSql.append(indentStr).append("    ").append(this.getPeriodSql(req)).append(", ").append(StringUtils.isNotBlank(req.getQueryColumn()) ? this.getQueryColumnSql(req) : StringUtils.EMPTY).append('\n');
		activeDeviceSql.append(indentStr).append("    COUNT(DISTINCT IF(fee > 0 OR givemoney > 0, kid, NULL)) periodPayCount, -- 期内设备付费数\n");
		activeDeviceSql.append(indentStr).append("    SUM(fee) periodPayFee1, -- 期内充值实付金额（分成前）\n");
		activeDeviceSql.append(indentStr).append("    SUM(fee * sharing) periodPayFee2, -- 期内充值实付金额（分成后）\n");
		activeDeviceSql.append(indentStr).append("    SUM(givemoney) periodPayGivemoney1, -- 期内充值代金券金额（分成前）\n");
		activeDeviceSql.append(indentStr).append("    SUM(givemoney * sharing) periodPayGivemoney2 -- 期内充值代金券金额（分成后）\n");
		activeDeviceSql.append(indentStr).append("FROM\n");
		activeDeviceSql.append(indentStr).append("    (\n");
		activeDeviceSql.append(indentStr).append("        SELECT\n");
		activeDeviceSql.append(indentStr).append("            d_reg_day day, d_reg_week week, d_reg_month month, d_reg_year year, d_kid kid, collect, d_game_main pgid, d_os os, d_game_sub gameid, d_chl_main parentchl, d_chl_sub chl, d_chl_base appchl, d_ad_id adid, d_ad_account advertiserid, fee, givemoney\n");
		activeDeviceSql.append(indentStr).append("        FROM\n");
		activeDeviceSql.append(indentStr).append("            ").append(yunYingProperties.getDeviceregisterlogintable()).append("\n");
		activeDeviceSql.append(indentStr).append("        WHERE\n");
		activeDeviceSql.append(indentStr).append("            d_spread_type = 1 AND d_reg_day >= ").append(req.getSTime()).append(" AND d_reg_day <= ").append(req.getETime()).append(" AND active_day >= ").append(req.getStartPayDate()).append(" AND active_day <= ").append(req.getEndPayDate()).append(this.indirectDeviceRegisterWhereSnippet(req, " ")).append('\n');
		activeDeviceSql.append(indentStr).append("    ) ub\n");
		activeDeviceSql.append(indentStr).append("    LEFT JOIN (SELECT CAST(id as Int16) AS id, (1 - COALESCE(sharing, 0)) sharing FROM dim_200_pangu_mysql_parent_game) pg ON ub.pgid = pg.id\n");
		activeDeviceSql.append(indentStr).append("    LEFT JOIN (SELECT manage investor, parent_code, chncode, dept_id deptId, dept_group_id userGroupId, real_name investorName, dept_name deptName, COALESCE(name,'-') userGroupName FROM v_dim_200_pangu_channel_user_dept_group) wpc ON wpc.parent_code = ub.parentchl AND wpc.chncode = ub.chl\n");
		activeDeviceSql.append(indentStr).append("WHERE\n");
		activeDeviceSql.append(indentStr).append("    1 = 1");
		if (req.getIsSys() != 1) {
			activeDeviceSql.append(" AND investor IN (").append(req.getUserIds()).append(")"); // -- 渠道权限
		}
		activeDeviceSql.append(this.selectComConditionWd(req, "")).append('\n');
		activeDeviceSql.append(indentStr).append("GROUP BY\n"); // 分组条件
		activeDeviceSql.append(indentStr).append("    ").append(this.getPeriodSql(req)).append(this.getGroupColumnSql(req));
		if (StringUtils.isNotBlank(req.getQueryColumn())) {
			activeDeviceSql.append(", ").append(req.getQueryColumn());
		}
		activeDeviceSql.append('\n');

		return activeDeviceSql;
	}


	private StringBuilder getActiveDeviceSql(AdDataDto req, String indentStr) {
		StringBuilder activeDeviceSql = new StringBuilder();
		activeDeviceSql.append(indentStr).append("SELECT\n");
		activeDeviceSql.append(indentStr).append("    ").append(this.getPeriodSql(req)).append(", ").append(StringUtils.isNotBlank(req.getQueryColumn()) ? this.getQueryColumnSql(req) : StringUtils.EMPTY).append('\n');
		activeDeviceSql.append(indentStr).append("    COUNT(DISTINCT kid_day) activedevices, -- 活跃设备数\n");
		activeDeviceSql.append(indentStr).append("    COUNT(DISTINCT IF(fee > 0 OR givemoney > 0, kid_day, NULL)) activepaydevices, -- 活跃付费设备数\n");
		activeDeviceSql.append(indentStr).append("    SUM(fee) activetotalfee, -- 活跃充值实付金额（分成前）\n");
		activeDeviceSql.append(indentStr).append("    SUM(fee * sharing) activesharfee, -- 活跃充值实付金额（分成后）\n");
		activeDeviceSql.append(indentStr).append("    SUM(givemoney) activetotalgivemoney, -- 活跃充值代金券金额（分成前）\n");
		activeDeviceSql.append(indentStr).append("    SUM(givemoney * sharing) activeshargivemoney -- 活跃充值代金券金额（分成后）\n");
		activeDeviceSql.append(indentStr).append("FROM\n");
		activeDeviceSql.append(indentStr).append("    (\n");
		activeDeviceSql.append(indentStr).append("        SELECT\n");
		activeDeviceSql.append(indentStr).append("            d_kid kid, concat(kid, '-', toString(day)) kid_day, active_day day, week, month, year, collect, d_ad_id adid, d_ad_account advertiserid, d_os os, d_game_main pgid, d_game_sub gameid, d_chl_main parentchl, d_chl_sub chl, d_chl_base appchl, fee, givemoney\n");
		activeDeviceSql.append(indentStr).append("        FROM\n");
		activeDeviceSql.append(indentStr).append("            ").append(yunYingProperties.getDeviceregisterlogintable()).append("\n");
		activeDeviceSql.append(indentStr).append("        WHERE\n");
		activeDeviceSql.append(indentStr).append("            d_spread_type = 1 AND active_day >= ").append(req.getSTime()).append(" AND active_day <= ").append(req.getETime()).append(this.indirectDeviceRegisterWhereSnippet(req, " ")).append('\n');
		activeDeviceSql.append(indentStr).append("    ) ub\n");
		activeDeviceSql.append(indentStr).append("    LEFT JOIN (SELECT CAST(id as Int16) AS id, (1 - COALESCE(sharing, 0)) sharing FROM dim_200_pangu_mysql_parent_game) pg ON ub.pgid = pg.id\n");
		activeDeviceSql.append(indentStr).append("    LEFT JOIN (SELECT manage investor, parent_code, chncode, dept_id deptId, dept_group_id userGroupId, real_name investorName, dept_name deptName, COALESCE(name,'-') userGroupName FROM v_dim_200_pangu_channel_user_dept_group) wpc ON wpc.parent_code = ub.parentchl AND wpc.chncode = ub.chl\n");
		activeDeviceSql.append(indentStr).append("WHERE\n");
		activeDeviceSql.append(indentStr).append("    1 = 1");
		if (req.getIsSys() != 1) {
			activeDeviceSql.append(" AND investor IN (").append(req.getUserIds()).append(")"); // -- 渠道权限
		}
		activeDeviceSql.append(this.selectComConditionWd(req, "")).append('\n');
		activeDeviceSql.append(indentStr).append("GROUP BY\n"); // 分组条件
		activeDeviceSql.append(indentStr).append("    ").append(this.getPeriodSql(req)).append(this.getGroupColumnSql(req));
		if (StringUtils.isNotBlank(req.getQueryColumn())) {
			activeDeviceSql.append(", ").append(req.getQueryColumn());
		}
		activeDeviceSql.append('\n');

		return activeDeviceSql;
	}

	/**
	 * 周期参数
	 */
	private StringBuilder getPeriodSql(AdDataDto req) {
		String period = req.getPeriod();
		StringBuilder sql = new StringBuilder();
		sql.append(period);
		return sql;
	}

	//类别参数
	private StringBuilder getQueryColumnSql(AdDataDto req) {
		StringBuilder sql = new StringBuilder();
		StringBuilder queryColumnSql = new StringBuilder();
		String queryColumn = req.getQueryColumn();

		if (StringUtils.isNotBlank(queryColumn)) {
			if (queryColumn.contains("deptId")) {
				queryColumnSql.append("deptName, ");
			}
			if (queryColumn.contains("investor")) {
				queryColumnSql.append("investorName, ");
			}
			if (queryColumn.contains("userGroupId")) {
				queryColumnSql.append("userGroupName, ");
			}
			queryColumnSql.append(queryColumn).append(", ");
		}
		sql.append(queryColumnSql);
		return sql;
	}

	//分组类别条件
	private StringBuilder getGroupColumnSql(AdDataDto req) {
		final String queryColumn = req.getQueryColumn();

		StringBuilder sql = new StringBuilder();
		StringBuilder groupColumnSql = new StringBuilder();

		if (StringUtils.isNotBlank(queryColumn)) {
			if (queryColumn.contains("deptId")) {
				groupColumnSql.append(", deptName");

			}
			if (queryColumn.contains("investor")) {
				groupColumnSql.append(", investorName");
			}
			if (queryColumn.contains("userGroupId")) {
				groupColumnSql.append(", userGroupName");

			}
			groupColumnSql.append(", ").append(queryColumn);
		}
		sql.append(groupColumnSql);
		return sql;
	}

	public String selectComConditionWd(AdDataDto req, String bieming) {
		final String deptIdArr = req.getDeptIdArr();
		final String userGroupIdArr = req.getUserGroupIdArr();
		final String investorArr = req.getInvestorArr();

		StringBuilder commCondSB = new StringBuilder();
		if (StringUtils.isNotBlank(deptIdArr)) {
			commCondSB.append(" AND deptId IN (").append(deptIdArr).append(")");
		}
		if (StringUtils.isNotBlank(userGroupIdArr)) {
			commCondSB.append(" AND userGroupId IN (").append(userGroupIdArr).append(")");
		}
		if (StringUtils.isNotBlank(investorArr)) {
			commCondSB.append(" AND investor IN (").append(investorArr).append(")");
		}
		return commCondSB.toString();
	}

	//筛选条件  类别指标筛选
	public String selectCommonCondition(AdDataDto req, String bieming) {
		final Integer os = req.getOs();
		final String pgidArr = req.getPgidArr();
		final String gameidArr = req.getGameidArr();
		final String parentchlArr = req.getParentchlArr();

		// 通用筛选条件
		StringBuilder commCondSB = new StringBuilder();
		if (os != null) {
			commCondSB.append(" AND os = ").append(os);
		}
		if (org.apache.commons.lang3.StringUtils.isNotBlank(pgidArr)) {
			commCondSB.append(" AND pgid IN (").append(pgidArr).append(")");
		}
		if (org.apache.commons.lang3.StringUtils.isNotBlank(gameidArr)) {
			commCondSB.append(" AND gameid IN (").append(gameidArr).append(")");
		}
		if (org.apache.commons.lang3.StringUtils.isNotBlank(parentchlArr)) {
			commCondSB.append(" AND parentchl IN ('").append(parentchlArr.replaceAll(",", "','")).append("')");
		}

		/*String commCond = commCondSB.toString();
		log.info("commCond: [{}]", commCond);*/
		return commCondSB.toString();
	}

	//筛选条件  类别指标筛选
	public String directDeviceRegisterWhereSnippet(AdDataDto req, String bieming) {
		final Integer os = req.getOs();
		final String pgidArr = req.getPgidArr();
		final String gameidArr = req.getGameidArr();
//		final String chl = req.getAppchlArr();
		final String appchl = req.getAppchlArr();
		final String parentchlArr = req.getParentchlArr();

		// 通用筛选条件
		StringBuilder commCondSB = new StringBuilder();

		if (os != null) {
			commCondSB.append(" AND os = ").append(os);
		}
		if (StringUtils.isNotBlank(pgidArr)) {
			commCondSB.append(" AND game_main IN (").append(pgidArr).append(")");
		}
		if (StringUtils.isNotBlank(gameidArr)) {
			commCondSB.append(" AND game_sub IN (").append(gameidArr).append(")");
		}
		if (StringUtils.isNotBlank(appchl)) {
			commCondSB.append(" AND chl_base = '").append(appchl).append("'");
		}
		if (StringUtils.isNotBlank(parentchlArr)) {
			commCondSB.append(" AND chl_main IN ('").append(parentchlArr.replaceAll(",", "','")).append("')");
		}

		/*String commCond = commCondSB.toString();
		log.info("commCond : [{}]", commCond);*/
		return commCondSB.toString();
	}

	public String indirectDeviceRegisterWhereSnippet(AdDataDto req, String bieming) {
		final Integer os = req.getOs();
		final String pgidArr = req.getPgidArr();
		final String gameidArr = req.getGameidArr();
		final String appchl = req.getAppchlArr();
		final String parentchlArr = req.getParentchlArr();

		// 通用筛选条件
		StringBuilder commCondSB = new StringBuilder();

		if (os != null) {
			commCondSB.append(" AND d_os = ").append(os);
		}
		if (StringUtils.isNotBlank(pgidArr)) {
			commCondSB.append(" AND d_game_main IN (" + pgidArr + ")");
		}
		if (StringUtils.isNotBlank(gameidArr)) {
			commCondSB.append(" AND d_game_sub IN (" + gameidArr + ")");
		}
		if (StringUtils.isNotBlank(appchl)) {
			commCondSB.append(" AND d_chl_base = '").append(appchl).append("'");
		}
		if (StringUtils.isNotBlank(parentchlArr)) {
			commCondSB.append(" AND d_chl_main IN ('").append(parentchlArr.replaceAll(",", "','")).append("')");
		}

		/*String commCond = commCondSB.toString();
		log.info("commCond : [{}]", commCond);*/
		return commCondSB.toString();
	}

	private AdDataVo ObjByRow(SqlRowSet rs, List<String> columns) {
		final Optional<List<String>> columnsOpt = Optional.ofNullable(columns);
		AdDataVo vo = new AdDataVo();

		vo.setPeriod(rs.getString("period"));
		vo.setInvestor(columnsOpt.filter(e -> e.contains("investor")).map(e -> rs.getString("investor")).filter(StringUtils::isNotBlank).orElse("-")); // 投放人id
		vo.setInvestorName(columnsOpt.filter(e -> e.contains("investorName")).map(e -> rs.getString("investorName")).filter(StringUtils::isNotBlank).orElse("-")); // 投放人名称
		vo.setDeptId(columnsOpt.filter(e -> e.contains("deptId")).map(e -> rs.getString("deptId")).filter(StringUtils::isNotBlank).orElse("-")); // 部门id
		vo.setDeptName(columnsOpt.filter(e -> e.contains("deptName")).map(e -> rs.getString("deptName")).filter(StringUtils::isNotBlank).orElse("-")); // 部门名称
		vo.setUserGroupId(columnsOpt.filter(e -> e.contains("userGroupId")).map(e -> rs.getString("userGroupId")).filter(StringUtils::isNotBlank).orElse("-")); // 组别id
		vo.setUserGroupName(columnsOpt.filter(e -> e.contains("userGroupName")).map(e -> rs.getString("userGroupName")).filter(StringUtils::isNotBlank).orElse("-")); // 组别名称
		vo.setParentchl(columnsOpt.filter(e -> e.contains("parentchl")).map(e -> rs.getString("parentchl")).filter(StringUtils::isNotBlank).orElse("-")); // 主渠道
		vo.setPgid(columnsOpt.filter(e -> e.contains("pgid")).map(e -> rs.getLong("pgid")).orElse(null)); // 父游戏id
		vo.setOs(columnsOpt.filter(e -> e.contains("os")).map(e -> rs.getInt("os")).orElse(null)); // 系统

		final BigDecimal rudeCost = rs.getBigDecimal("rudeCost");
		vo.setRudeCost(BigDecimal.ZERO.compareTo(rudeCost) == 0 ? BigDecimal.ZERO : rudeCost);
		final BigDecimal cost = rs.getBigDecimal("cost");
		vo.setCost(BigDecimal.ZERO.compareTo(cost) == 0 ? BigDecimal.ZERO : cost);
		vo.setUsrnamenums(rs.getInt("usrnamenums")); // 新增注册设备数
		vo.setCreateRoleCount(rs.getInt("createRoleCount")); // 新增注册创角数
		vo.setCertifiedCount(rs.getInt("certifiedCount")); // 新增注册实名数
		vo.setNotCertifiedCount(rs.getInt("notCertifiedCount")); // 注册未实名数
		vo.setYoungCount(rs.getInt("youngCount")); // 未成年人数
		vo.setCreateRoleRate(rs.getBigDecimal("createRoleRate")); // 新增创角率
		vo.setCertifiedRate(rs.getBigDecimal("certifiedRate")); // 新增实名制转化率
		vo.setNewPayCount(rs.getInt("newPayCount")); // 新增付费设备数
		vo.setNewPayAmount(rs.getBigDecimal("newPayAmount")); // 新增充值实付金额
		vo.setNewPayGivemoney(rs.getBigDecimal("newPayGivemoney")); // 新增充值代金券金额
		vo.setWeektotalfee(rs.getBigDecimal("weektotalfee")); // 当周充值实付金额
		vo.setWeekPayGivemoney(rs.getBigDecimal("weekPayGivemoney")); // 当周充值代金券金额
		vo.setMonthtotalfee(rs.getBigDecimal("monthtotalfee")); // 当月充值实付金额
		vo.setMonthPayGivemoney(rs.getBigDecimal("monthPayGivemoney")); // 当月充值代金券金额
		vo.setPeriodPayCount(rs.getInt("periodPayCount")); // 期内付费设备数
		vo.setDuplicateDeviceRatio(rs.getBigDecimal("duplicateDeviceRatio")); //设备重复率
		vo.setPeriodPayFee(rs.getBigDecimal("periodPayFee")); // 期内充值实付金额
		vo.setPeriodPayGivemoney(rs.getBigDecimal("periodPayGivemoney")); // 期内充值代金券金额
		vo.setTotalPayfee(rs.getBigDecimal("totalPayfee")); // 累计充值实付金额
		vo.setPeriodPayCose(rs.getBigDecimal("periodPayCose")); // 增加期内付费成本
		vo.setDuplicateDeviceCount(rs.getInt("duplicateDeviceCount"));// 增加期内付费成本
		vo.setTotalPayGivemoney(rs.getBigDecimal("totalPayGivemoney")); // 累计充值代金券金额
		vo.setActivedevices(rs.getInt("activedevices")); // 活跃设备数
		vo.setActivePayCount(rs.getInt("activePayCount")); // 活跃付费设备数
		vo.setActivetotalfee(rs.getBigDecimal("activetotalfee")); // 活跃充值实付金额
		vo.setActivePayGivemoney(rs.getBigDecimal("activePayGivemoney")); // 活跃充值代金券金额

		vo.setDeviceCose(rs.getBigDecimal("deviceCose")); // 设备成本
		vo.setRegPayRatio(rs.getBigDecimal("regPayRatio")); // 新增付费率
		vo.setPeriodPayRate(rs.getBigDecimal("periodPayRate")); // 期内付费率
		vo.setActivePayRate(rs.getBigDecimal("activePayRate")); // 活跃付费率
		vo.setRegarpu(rs.getBigDecimal("regarpu")); // 新增注册ARPU
		vo.setNewPayArppu(rs.getBigDecimal("newPayArppu")); // 新增付费ARPPU
		vo.setActarpu(rs.getBigDecimal("actarpu")); // 活跃设备ARPU
		vo.setActivePayArppu(rs.getBigDecimal("activePayArppu")); // 活跃付费ARPPU
		vo.setRoi1(rs.getBigDecimal("roi1")); // 首日ROI，新增充值金额/返点后消耗
		vo.setWeekRoi(rs.getBigDecimal("weekRoi")); // 当周ROI，当周充值金额/返点后消耗
		vo.setMonthRoi(rs.getBigDecimal("monthRoi")); // 当月ROI，当月充值金额/返点后消耗
		vo.setAllRoi(rs.getBigDecimal("allRoi")); // 累计充值ROI，累计充值金额/返点后消耗 allRoi
		vo.setRetention2Ratio(rs.getBigDecimal("retention2Ratio")); // 次留

		return vo;
	}

	// 入参时间转Str
	private String convertQueryTime(Long queryTime) {
		if (Objects.nonNull(queryTime)) {
			String time = String.valueOf(queryTime.intValue());
			if (StringUtils.isNotBlank(time)) {
				return String.format("%s-%s-%s", time.substring(0, 4), time.substring(4, 6), time.substring(6, 8));
			}
		}
		return null;
	}

}
